{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd,xlwings as xw,订单完成_函数 as dd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "wb=xw.Book(r'c:\\Users\\UUPT\\Desktop\\应付工作\\8月\\加减款\\2024.8月加减款汇总表-8.29.xlsx')\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [],
   "source": [
    "#直营跑男加减款\n",
    "df1=wb.sheets('8.31').range('a2:J59').options(pd.DataFrame).value.reset_index()\n",
    "df1=df1.iloc[0:] #因为是合并单元格表头，所以从第二行开始取"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [],
   "source": [
    "#直营用户加减款\n",
    "df2=wb.sheets('8.31').range('I2:r59').options(pd.DataFrame).value.reset_index()\n",
    "df2=df2.iloc[0:] #因为是合并单元格表头，所以从第二行开始取"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {},
   "outputs": [],
   "source": [
    "#代理跑男加减款\n",
    "df3=wb.sheets('8.31').range('t2:ac39').options(pd.DataFrame).value.reset_index()\n",
    "df3=df3.iloc[0:] #因为是合并单元格表头，所以从第二行开始取"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {},
   "outputs": [],
   "source": [
    "#代理用户加减款\n",
    "df4=wb.sheets('8.31').range('ab2:Ak39').options(pd.DataFrame).value.reset_index()\n",
    "df4=df4.iloc[0:] #因为是合并单元格表头，所以从第二行开始取"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {},
   "outputs": [],
   "source": [
    "直营跑男提现=dd.通用凭证模版(dd.clm,'应付凭证',摘要='支付'+df1['部门']+'跑男提现',科目编码='224101',借贷方向='借方',本币=df1['跑男提现'],\n",
    "               部门编码=df1['部门编码'],部门=df1['部门']) \n",
    "直营跑男补贴=dd.通用凭证模版(dd.clm,'应付凭证',摘要='支付'+df1['部门']+'跑男补贴',科目编码='660154',借贷方向='借方',本币=df1['补贴跑男'],\n",
    "               部门编码=df1['部门编码'],部门=df1['部门'],项目编码=df1['部门编码'],项目=df1['部门'])   \n",
    "\n",
    "一dianmingle02合计=dd.通用凭证模版(dd.clm,'应付凭证',摘要='支付跑男提现',科目编码='101223',借贷方向='贷方',本币=[df1['dianmingle02'].sum()])\n",
    "\n",
    "一招商银行合计=dd.通用凭证模版(dd.clm,'应付凭证',摘要='支付跑男提现',科目编码='100207',借贷方向='贷方',本币=[df1['招商银行'].sum()])\n",
    "\n",
    "一郑好办支付宝合计=dd.通用凭证模版(dd.clm,'应付凭证',摘要='支付跑男提现',科目编码='101222',借贷方向='贷方',本币=[df1['郑好办支付宝'].sum()])\n",
    "\n",
    "一银联合计=dd.通用凭证模版(dd.clm,'应付凭证',摘要='支付跑男提现',科目编码='101219',借贷方向='贷方',本币=[df1['银联'].sum()])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {},
   "outputs": [],
   "source": [
    "直营用户退款=dd.通用凭证模版(dd.clm,'应付凭证',摘要='支付'+df1['部门']+'用户退款',科目编码='224117',借贷方向='借方',本币=df2['用户退款']) \n",
    "直营用户补贴=dd.通用凭证模版(dd.clm,'应付凭证',摘要='支付'+df1['部门']+'用户补贴',科目编码='660153',借贷方向='借方',本币=df2['补贴用户'],\n",
    "               部门编码=df2['部门编码'],部门=df2['部门'],项目编码=df2['部门编码'],项目=df2['部门']) \n",
    "\n",
    "二dianmingle02合计=dd.通用凭证模版(dd.clm,'应付凭证',摘要='支付用户退款',科目编码='101223',借贷方向='贷方',本币=[df2['dianmingle02'].sum()])\n",
    "\n",
    "二招商银行合计=dd.通用凭证模版(dd.clm,'应付凭证',摘要='支付用户退款',科目编码='100207',借贷方向='贷方',本币=[df2['招商银行'].sum()])\n",
    "\n",
    "二郑好办支付宝合计=dd.通用凭证模版(dd.clm,'应付凭证',摘要='支付用户退款',科目编码='101222',借贷方向='贷方',本币=[df2['郑好办支付宝'].sum()])\n",
    "\n",
    "二银联合计=dd.通用凭证模版(dd.clm,'应付凭证',摘要='支付用户退款',科目编码='101219',借贷方向='贷方',本币=[df2['银联'].sum()])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {},
   "outputs": [],
   "source": [
    "代理跑男提现=dd.通用凭证模版(dd.clm,'应付凭证',摘要='支付'+df3['往来单位']+'跑男提现',科目编码=' 22413002',借贷方向='借方',本币=df3['跑男提现'],\n",
    "               往来单位编码=df3['往来单位编码'],往来单位=df3['往来单位']) \n",
    "代理跑男补贴=dd.通用凭证模版(dd.clm,'应付凭证',摘要='支付'+df3['往来单位']+'跑男补贴',科目编码=' 22413003',借贷方向='借方',本币=df3['补贴跑男'],\n",
    "               往来单位编码=df3['往来单位编码'],往来单位=df3['往来单位']) \n",
    "\n",
    "三dianmingle02合计=dd.通用凭证模版(dd.clm,'应付凭证',摘要='支付代理商跑男提现',科目编码='101223',借贷方向='贷方',本币=[df3['dianmingle02'].sum()])\n",
    "\n",
    "三招商银行合计=dd.通用凭证模版(dd.clm,'应付凭证',摘要='支付代理商跑男提现',科目编码='100207',借贷方向='贷方',本币=[df3['招商银行'].sum()])\n",
    "\n",
    "三郑好办支付宝合计=dd.通用凭证模版(dd.clm,'应付凭证',摘要='支付代理商跑男提现',科目编码='101222',借贷方向='贷方',本币=[df3['郑好办支付宝'].sum()])\n",
    "\n",
    "三银联合计=dd.通用凭证模版(dd.clm,'应付凭证',摘要='支付代理商跑男提现',科目编码='101219',借贷方向='贷方',本币=[df3['银联'].sum()])\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [],
   "source": [
    "代理用户退款=dd.通用凭证模版(dd.clm,'应付凭证',摘要='支付'+df3['往来单位']+'用户退款',科目编码=' 22413001',借贷方向='借方',本币=df4['用户退款']) \n",
    "代理用户补贴=dd.通用凭证模版(dd.clm,'应付凭证',摘要='支付'+df3['往来单位']+'用户补贴',科目编码=' 22413003',借贷方向='借方',本币=df4['补贴用户']) \n",
    "\n",
    "四dianmingle02合计=dd.通用凭证模版(dd.clm,'应付凭证',摘要='支付代理商用户退款',科目编码='101223',借贷方向='贷方',本币=[df4['dianmingle02'].sum()])\n",
    "\n",
    "四招商银行合计=dd.通用凭证模版(dd.clm,'应付凭证',摘要='支付代理商用户退款',科目编码='100207',借贷方向='贷方',本币=[df4['招商银行'].sum()])\n",
    "\n",
    "四郑好办支付宝合计=dd.通用凭证模版(dd.clm,'应付凭证',摘要='支付代理商用户退款',科目编码='101222',借贷方向='贷方',本币=[df4['郑好办支付宝'].sum()])\n",
    "\n",
    "四银联合计=dd.通用凭证模版(dd.clm,'应付凭证',摘要='支付代理商用户退款',科目编码='101219',借贷方向='贷方',本币=[df4['银联'].sum()])\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {},
   "outputs": [],
   "source": [
    "凭证=pd.concat([直营跑男提现,直营跑男补贴,一dianmingle02合计,一招商银行合计,一郑好办支付宝合计,一银联合计,直营用户退款,直营用户补贴,二dianmingle02合计,二招商银行合计,二郑好办支付宝合计,二银联合计,\n",
    "              代理跑男提现,代理跑男补贴,三dianmingle02合计,三招商银行合计,三郑好办支付宝合计,三银联合计,代理用户退款,代理用户补贴,四dianmingle02合计,四招商银行合计,四郑好办支付宝合计,四银联合计]) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {},
   "outputs": [],
   "source": [
    "凭证.to_clipboard()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
